
package com.gcloud.mesh.analysis.dao;

import com.gcloud.framework.db.PageResult;
import com.gcloud.framework.db.dao.impl.JdbcBaseDaoImpl;
import com.gcloud.mesh.analysis.entity.SubhealthAlertEntity;
import com.gcloud.mesh.header.vo.analysis.SubhealthAlertCountVo;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class SubhealthAlertDao extends JdbcBaseDaoImpl<SubhealthAlertEntity, String> {

    public Integer count(String deviceType) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT COUNT(*) FROM dcs_subhealth_alerts");
        if (deviceType != null) {
			sb.append(" WHERE device_type='").append(deviceType).append("'");
		}
        return this.countBySql(sb.toString(), new ArrayList<Object>());
    }
    
//    public <E> PageResult<E> page(String deviceType, String meter, String level, String startTime, String endTime, 
//    		Integer pageNo, Integer pageSize, Class<E> clazz) {
//        List<Object> values = new ArrayList<>();
//
//        StringBuilder sb = new StringBuilder();
////        sb.append("SELECT a.* FROM `dcs_subhealth_alerts` a ");
//        // 此处查询建立的索引，忽随意改动字段顺序或增加字段
//        sb.append(" SELECT a.create_time, a.device_type, a.meter,a. meter_name, a.resource_id, a.resource_name, a.level, a.content, a.id FROM `dcs_subhealth_alerts` a ");
//        sb.append(" WHERE 1=1 ");
//
//        if (StringUtils.isNotBlank(deviceType)) {
//            sb.append(" AND a.device_type = ?");
//            values.add(deviceType);
//        }
//        if (StringUtils.isNotBlank(meter)) {
//            sb.append(" AND a.meter = ?");
//            values.add(meter);
//        }
//        if (StringUtils.isNotBlank(level)) {
//            sb.append(" AND a.level = ?");
//            values.add(level);
//        }
//        if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
//            sb.append(" AND a.create_time BETWEEN ? AND ?");
//            values.add(startTime);
//            values.add(endTime);
//        }
//
//        sb.append(" ORDER BY a.create_time DESC");
//        return this.findBySql(sb.toString(), values, pageNo, pageSize, clazz);
//    }
    
    public <E> PageResult<E> page(String deviceType, String meter, String level, String startTime, String endTime, 
    		Integer pageNo, Integer pageSize, Class<E> clazz) {
        List<Object> values = new ArrayList<>();

        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT i.*,ii.type_count FROM ");
        sb.append(" ( SELECT 1 sync, a.* FROM `dcs_subhealth_alerts` a ) i ");
        sb.append(" LEFT JOIN ");
        sb.append(" ( SELECT 1 sync, GROUP_CONCAT(cte.type_count) type_count FROM ");
        sb.append(" ( SELECT device_type, CONCAT_WS(':', device_type, count(id)) type_count FROM `dcs_subhealth_alerts` GROUP BY device_type ) cte ) ii ");
        sb.append(" ON i.sync = ii.sync ");

        sb.append(" WHERE 1=1 ");

        if (StringUtils.isNotBlank(deviceType)) {
            sb.append(" AND i.device_type = ?");
            values.add(deviceType);
        }
        if (StringUtils.isNotBlank(meter)) {
            sb.append(" AND i.meter = ?");
            values.add(meter);
        }
        if (StringUtils.isNotBlank(level)) {
            sb.append(" AND i.level = ?");
            values.add(level);
        }
        if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
            sb.append(" AND i.create_time BETWEEN ? AND ?");
            values.add(startTime);
            values.add(endTime);
        }

        sb.append(" ORDER BY i.create_time DESC");
        return this.findBySql(sb.toString(), values, pageNo, pageSize, clazz);
    }
    
    
    public void deleteByDate(String date) {
		StringBuffer sql = new StringBuffer();
		sql.append("delete from dcs_subhealth_alerts where create_time <= ? ");	
		this.jdbcTemplate.update(sql.toString(), date);
    }
    
    public List<SubhealthAlertCountVo> countByType(String deviceType) {
        StringBuilder sb = new StringBuilder();
        List<Object> values = new ArrayList<Object>();
        sb.append("SELECT a.device_type, count(a.id) count FROM `dcs_subhealth_alerts` a  WHERE 1=1 ");
        if (deviceType != null) {
        	sb.append(" AND a.device_type = ? ");
            values.add(deviceType);
		}
        sb.append(" GROUP BY a.device_type ORDER BY count DESC");
        return this.findBySql(sb.toString(),  values, SubhealthAlertCountVo.class);
    }
    
    public List<SubhealthAlertCountVo> countByType() {
        return countByType(null);
    }

}
